package com.zyh.day04.sql

import org.apache.spark.sql.SparkSession

object OverTest {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder()
      .appName("where")
      .master("local[*]")
      .getOrCreate()
    import spark.implicits._

    val df = List((1, "zs", true, 1, 15000), (2, "ls", false, 2, 17000), (3, "ww", false, 2, 14000), (4, "zl", false, 1, 18000),
      (5, "win7", false, 1, 18000)).toDF("id", "name", "sex", "dept", "salary")

    df.createTempView("t_emp")

//    val sql =
//      """
//        |select id,name,salary,dept,
//        |dense_rank() over(partition by dept order by salary desc) x1
//        |from t_emp
//      """.stripMargin
    val sql =
      """
        |select e.*,
        |max(salary) over(partition by dept order by salary desc) max_salary,
        |sum(salary) over(partition by dept) sum_salary
        |from t_emp e
        |""".stripMargin
    spark.sql(sql).show()
    spark.close()
  }
}
